
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;

// export a .csv file for each senzor in the database

public class ExportCsv 
{
	public static String s[] = null;
	public static int rowCount = -1;
	public static int i;
	public static void main (String ars[]) throws IOException, SQLException
	{
		Connection conn = null;
	    String url = "jdbc:mysql://localhost";
	    String dbName = "consum";
	    String driver = "com.mysql.jdbc.Driver";
	    String userName = "root";
	    String password = "oanamondy";
	    try {
	      Class.forName(driver).newInstance();
	      conn = DriverManager.getConnection(url+dbName,userName,password);
	      System.out.println("Connected to the database");
	      //new countFromTable();
	      for (i=0; i<rowCount; i++)
			{
				export_csv_file("D:\\", null, null);
			}
	      conn.close();
	      System.out.println("Disconnected from database");
	    } catch (Exception e) {
	      e.printStackTrace();
	    }
		
	}
	
	void countFromTable(Connection conn) throws SQLException
	{
		//tabel = "scenariu"
		int i;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt=conn.createStatement();
			//query to count the total number of senzors, put all the values into an array
			rs = stmt.executeQuery("SELECT COUNT (DISTINCT senzor) FROM scenariu");
			rs.next();
			rowCount = rs.getInt(1);
		 //	        
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}		
			
			for (i=0; i<rowCount;i++)
			{
				rs = stmt.executeQuery("SELECT (DISTINCT senzor) FROM scenariu");
				String value = rs.getObject(i).toString();
				s[i] = value;
				rs.next();
			}
		} finally {
	        rs.close();
			stmt.close();
		}
	}
	
	public static void export_csv_file(String path,Connection conn, String senzor) throws IOException, SQLException
	{
		//export into the csv file all informations about a senzor
		Statement stmt = null;
		ResultSet rs = null;
		FileWriter writer = new FileWriter(path);
		try {
			//campuri ale tabelei scenariu : id, data, ora, senzor, valoare
			//select senzor where senzor_name=s[i]. for each s[i] create new .csv and insert information from db.
			
			writer.append("ID Senzor");
			writer.append("||");
			writer.append("Data");
			writer.append("||");				
			writer.append("Ora");
			writer.append("||");
			writer.append("Valoare");				
			
			stmt=conn.createStatement();
			//query to count the total number of senzors, put all the values into an array
									
			rs = stmt.executeQuery("SELECT SELECT CONCAT(CONCAT(CONCAT(CURDATE(),'T'),`ora` ),'Z,'),`valoare`" +
							 "into OUTFILE '"+senzor+"' " +
							 "FIELDS TERMINATED BY '||' LINES TERMINATED BY '\n' " +
							 "FROM scenariu WHERE senzor = '%s';");
			rs.next();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
			writer.flush();
			writer.close();
	}	
}
